{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!conda install -c conda-forge splink=4.0 --yes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from splink import splink_datasets\n",
    "\n",
    "df = splink_datasets.historical_50k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from splink import DuckDBAPI\n",
    "db_api = DuckDBAPI()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from splink import DuckDBAPI, block_on\n",
    "from splink.blocking_analysis import (\n",
    "    cumulative_comparisons_to_be_scored_from_blocking_rules_chart,\n",
    ")\n",
    "\n",
    "blocking_rules = [\n",
    "    block_on(\"substr(first_name,1,3)\", \"substr(surname,1,4)\"),\n",
    "    block_on(\"surname\", \"dob\"),\n",
    "    block_on(\"first_name\", \"dob\"),\n",
    "    block_on(\"postcode_fake\", \"first_name\"),\n",
    "    block_on(\"postcode_fake\", \"surname\"),\n",
    "    block_on(\"dob\", \"birth_place\"),\n",
    "    block_on(\"substr(postcode_fake,1,3)\", \"dob\"),\n",
    "    block_on(\"substr(postcode_fake,1,3)\", \"first_name\"),\n",
    "    block_on(\"substr(postcode_fake,1,3)\", \"surname\"),\n",
    "    block_on(\"substr(first_name,1,2)\", \"substr(surname,1,2)\", \"substr(dob,1,4)\"),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import splink.comparison_library as cl\n",
    "\n",
    "from splink import Linker, SettingsCreator\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    blocking_rules_to_generate_predictions=blocking_rules,\n",
    "    comparisons=[\n",
    "        cl.NameComparison(\"first_name\").configure(term_frequency_adjustments=False),\n",
    "        cl.NameComparison(\"surname\").configure(term_frequency_adjustments=False),\n",
    "        cl.DateOfBirthComparison(\"dob\", input_is_string=True),\n",
    "        cl.PostcodeComparison(\"postcode_fake\"),\n",
    "        cl.ExactMatch(\"birth_place\").configure(term_frequency_adjustments=False),\n",
    "        cl.ExactMatch(\"occupation\").configure(term_frequency_adjustments=False),\n",
    "    ],\n",
    "    retain_intermediate_calculation_columns=True,\n",
    ")\n",
    "\n",
    "linker = Linker(df, settings, db_api=db_api)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Probability two random records match is estimated to be  0.000136.\n",
      "This means that amongst all possible pairwise record comparisons, one in 7,362.31 are expected to match.  With 1,279,041,753 total possible comparisons, we expect a total of around 173,728.33 matching pairs\n"
     ]
    }
   ],
   "source": [
    "linker.training.estimate_probability_two_random_records_match(\n",
    "    [\n",
    "        \"l.first_name = r.first_name and l.surname = r.surname and l.dob = r.dob\",\n",
    "        \"substr(l.first_name,1,2) = substr(r.first_name,1,2) and l.surname = r.surname and substr(l.postcode_fake,1,2) = substr(r.postcode_fake,1,2)\",\n",
    "        \"l.dob = r.dob and l.postcode_fake = r.postcode_fake\",\n",
    "    ],\n",
    "    recall=0.6,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "----- Estimating u probabilities using random sampling -----\n",
      "\n",
      "Estimated u probabilities using random sampling\n",
      "\n",
      "Your model is not yet fully trained. Missing estimates for:\n",
      "    - first_name (no m values are trained).\n",
      "    - surname (no m values are trained).\n",
      "    - dob (no m values are trained).\n",
      "    - postcode_fake (no m values are trained).\n",
      "    - birth_place (no m values are trained).\n",
      "    - occupation (no m values are trained).\n"
     ]
    }
   ],
   "source": [
    "linker.training.estimate_u_using_random_sampling(max_pairs=5e6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "----- Starting EM training session -----\n",
      "\n",
      "Estimating the m probabilities of the model by blocking on:\n",
      "(l.\"first_name\" = r.\"first_name\") AND (l.\"surname\" = r.\"surname\")\n",
      "\n",
      "Parameter estimates will be made for the following comparison(s):\n",
      "    - dob\n",
      "    - postcode_fake\n",
      "    - birth_place\n",
      "    - occupation\n",
      "\n",
      "Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules: \n",
      "    - first_name\n",
      "    - surname\n",
      "\n",
      "Iteration 1: Largest change in params was -0.515 in probability_two_random_records_match\n",
      "Iteration 2: Largest change in params was -0.0362 in probability_two_random_records_match\n",
      "Iteration 3: Largest change in params was 0.0135 in the m_probability of birth_place, level `Exact match on birth_place`\n",
      "Iteration 4: Largest change in params was -0.00654 in the m_probability of birth_place, level `All other comparisons`\n",
      "Iteration 5: Largest change in params was 0.00378 in the m_probability of birth_place, level `Exact match on birth_place`\n",
      "Iteration 6: Largest change in params was -0.00234 in the m_probability of birth_place, level `All other comparisons`\n",
      "Iteration 7: Largest change in params was -0.00148 in the m_probability of birth_place, level `All other comparisons`\n",
      "Iteration 8: Largest change in params was -0.00095 in the m_probability of dob, level `Abs date difference <= 10 year`\n",
      "Iteration 9: Largest change in params was -0.000633 in the m_probability of dob, level `Abs date difference <= 10 year`\n",
      "Iteration 10: Largest change in params was -0.000419 in the m_probability of dob, level `Abs date difference <= 10 year`\n",
      "Iteration 11: Largest change in params was -0.000277 in the m_probability of dob, level `Abs date difference <= 10 year`\n",
      "Iteration 12: Largest change in params was -0.000183 in the m_probability of dob, level `Abs date difference <= 10 year`\n",
      "Iteration 13: Largest change in params was -0.00012 in the m_probability of dob, level `Abs date difference <= 10 year`\n",
      "Iteration 14: Largest change in params was -7.92e-05 in the m_probability of dob, level `Abs date difference <= 10 year`\n",
      "\n",
      "EM converged after 14 iterations\n",
      "\n",
      "Your model is not yet fully trained. Missing estimates for:\n",
      "    - first_name (no m values are trained).\n",
      "    - surname (no m values are trained).\n"
     ]
    }
   ],
   "source": [
    "training_blocking_rule = block_on(\"first_name\", \"surname\")\n",
    "training_session_names = (\n",
    "    linker.training.estimate_parameters_using_expectation_maximisation(\n",
    "        training_blocking_rule, estimate_without_term_frequencies=True\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "----- Starting EM training session -----\n",
      "\n",
      "Estimating the m probabilities of the model by blocking on:\n",
      "l.\"dob\" = r.\"dob\"\n",
      "\n",
      "Parameter estimates will be made for the following comparison(s):\n",
      "    - first_name\n",
      "    - surname\n",
      "    - postcode_fake\n",
      "    - birth_place\n",
      "    - occupation\n",
      "\n",
      "Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules: \n",
      "    - dob\n",
      "\n",
      "Iteration 1: Largest change in params was -0.36 in the m_probability of first_name, level `Exact match on first_name`\n",
      "Iteration 2: Largest change in params was 0.0382 in the m_probability of first_name, level `All other comparisons`\n",
      "Iteration 3: Largest change in params was 0.00824 in the m_probability of surname, level `All other comparisons`\n",
      "Iteration 4: Largest change in params was 0.00266 in the m_probability of surname, level `All other comparisons`\n",
      "Iteration 5: Largest change in params was 0.000806 in the m_probability of surname, level `All other comparisons`\n",
      "Iteration 6: Largest change in params was 0.00024 in the m_probability of surname, level `All other comparisons`\n",
      "Iteration 7: Largest change in params was 7.1e-05 in the m_probability of surname, level `All other comparisons`\n",
      "\n",
      "EM converged after 7 iterations\n",
      "\n",
      "Your model is fully trained. All comparisons have at least one estimate for their m and u values\n"
     ]
    }
   ],
   "source": [
    "training_blocking_rule = block_on(\"dob\")\n",
    "training_session_dob = (\n",
    "    linker.training.estimate_parameters_using_expectation_maximisation(\n",
    "        training_blocking_rule, estimate_without_term_frequencies=True\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'link_type': 'dedupe_only',\n",
       " 'probability_two_random_records_match': 0.00013582694460587586,\n",
       " 'retain_matching_columns': True,\n",
       " 'retain_intermediate_calculation_columns': True,\n",
       " 'additional_columns_to_retain': [],\n",
       " 'sql_dialect': 'duckdb',\n",
       " 'linker_uid': '66au8ius',\n",
       " 'em_convergence': 0.0001,\n",
       " 'max_iterations': 25,\n",
       " 'bayes_factor_column_prefix': 'bf_',\n",
       " 'term_frequency_adjustment_column_prefix': 'tf_',\n",
       " 'comparison_vector_value_column_prefix': 'gamma_',\n",
       " 'unique_id_column_name': 'unique_id',\n",
       " 'source_dataset_column_name': 'source_dataset',\n",
       " 'blocking_rules_to_generate_predictions': [{'blocking_rule': '(SUBSTR(l.first_name, 1, 3) = SUBSTR(r.first_name, 1, 3)) AND (SUBSTR(l.surname, 1, 4) = SUBSTR(r.surname, 1, 4))',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"surname\" = r.\"surname\") AND (l.\"dob\" = r.\"dob\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"first_name\" = r.\"first_name\") AND (l.\"dob\" = r.\"dob\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"postcode_fake\" = r.\"postcode_fake\") AND (l.\"first_name\" = r.\"first_name\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"postcode_fake\" = r.\"postcode_fake\") AND (l.\"surname\" = r.\"surname\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"dob\" = r.\"dob\") AND (l.\"birth_place\" = r.\"birth_place\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(SUBSTR(l.postcode_fake, 1, 3) = SUBSTR(r.postcode_fake, 1, 3)) AND (l.\"dob\" = r.\"dob\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(SUBSTR(l.postcode_fake, 1, 3) = SUBSTR(r.postcode_fake, 1, 3)) AND (l.\"first_name\" = r.\"first_name\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(SUBSTR(l.postcode_fake, 1, 3) = SUBSTR(r.postcode_fake, 1, 3)) AND (l.\"surname\" = r.\"surname\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(SUBSTR(l.first_name, 1, 2) = SUBSTR(r.first_name, 1, 2)) AND (SUBSTR(l.surname, 1, 2) = SUBSTR(r.surname, 1, 2)) AND (SUBSTR(l.dob, 1, 4) = SUBSTR(r.dob, 1, 4))',\n",
       "   'sql_dialect': 'duckdb'}],\n",
       " 'comparisons': [{'output_column_name': 'first_name',\n",
       "   'comparison_levels': [{'sql_condition': '\"first_name_l\" IS NULL OR \"first_name_r\" IS NULL',\n",
       "     'label_for_charts': 'first_name is NULL',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"first_name_l\" = \"first_name_r\"',\n",
       "     'label_for_charts': 'Exact match on first_name',\n",
       "     'm_probability': 0.5457057743059716,\n",
       "     'u_probability': 0.011951030823329812,\n",
       "     'tf_adjustment_column': 'first_name',\n",
       "     'tf_adjustment_weight': 1.0},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"first_name_l\", \"first_name_r\") >= 0.92',\n",
       "     'label_for_charts': 'Jaro-Winkler distance of first_name >= 0.92',\n",
       "     'm_probability': 0.06098051520443211,\n",
       "     'u_probability': 0.0018262673367931644},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"first_name_l\", \"first_name_r\") >= 0.88',\n",
       "     'label_for_charts': 'Jaro-Winkler distance of first_name >= 0.88',\n",
       "     'm_probability': 0.07502792175772721,\n",
       "     'u_probability': 0.003353844214458144},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"first_name_l\", \"first_name_r\") >= 0.7',\n",
       "     'label_for_charts': 'Jaro-Winkler distance of first_name >= 0.7',\n",
       "     'm_probability': 0.120309986619356,\n",
       "     'u_probability': 0.020885448595733304},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.19797580211251312,\n",
       "     'u_probability': 0.9619834090296856}],\n",
       "   'comparison_description': 'NameComparison'},\n",
       "  {'output_column_name': 'surname',\n",
       "   'comparison_levels': [{'sql_condition': '\"surname_l\" IS NULL OR \"surname_r\" IS NULL',\n",
       "     'label_for_charts': 'surname is NULL',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"surname_l\" = \"surname_r\"',\n",
       "     'label_for_charts': 'Exact match on surname',\n",
       "     'm_probability': 0.7714400787402513,\n",
       "     'u_probability': 0.0006788109515158729,\n",
       "     'tf_adjustment_column': 'surname',\n",
       "     'tf_adjustment_weight': 1.0},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"surname_l\", \"surname_r\") >= 0.92',\n",
       "     'label_for_charts': 'Jaro-Winkler distance of surname >= 0.92',\n",
       "     'm_probability': 0.09368190481882498,\n",
       "     'u_probability': 0.0002900130152182439},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"surname_l\", \"surname_r\") >= 0.88',\n",
       "     'label_for_charts': 'Jaro-Winkler distance of surname >= 0.88',\n",
       "     'm_probability': 0.039136395099805245,\n",
       "     'u_probability': 0.00048319241644015245},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"surname_l\", \"surname_r\") >= 0.7',\n",
       "     'label_for_charts': 'Jaro-Winkler distance of surname >= 0.7',\n",
       "     'm_probability': 0.023569512976655334,\n",
       "     'u_probability': 0.017232968503952374},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.07217210836446314,\n",
       "     'u_probability': 0.9813150151128733}],\n",
       "   'comparison_description': 'NameComparison'},\n",
       "  {'output_column_name': 'dob',\n",
       "   'comparison_levels': [{'sql_condition': 'try_strptime(\"dob_l\", \\'%Y-%m-%d\\') IS NULL OR try_strptime(\"dob_r\", \\'%Y-%m-%d\\') IS NULL',\n",
       "     'label_for_charts': 'transformed dob is NULL',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"dob_l\" = \"dob_r\"',\n",
       "     'label_for_charts': 'Exact match on date of birth',\n",
       "     'm_probability': 0.6796640528638345,\n",
       "     'u_probability': 0.0023542362733926883},\n",
       "    {'sql_condition': 'damerau_levenshtein(\"dob_l\", \"dob_r\") <= 1',\n",
       "     'label_for_charts': 'DamerauLevenshtein distance <= 1',\n",
       "     'm_probability': 0.2736730864202431,\n",
       "     'u_probability': 0.02425500685606134},\n",
       "    {'sql_condition': 'ABS(EPOCH(try_strptime(\"dob_l\", \\'%Y-%m-%d\\')) - EPOCH(try_strptime(\"dob_r\", \\'%Y-%m-%d\\'))) <= 2629800.0',\n",
       "     'label_for_charts': 'Abs date difference <= 1 month',\n",
       "     'm_probability': 0.002677966995147039,\n",
       "     'u_probability': 0.0023411035361826224},\n",
       "    {'sql_condition': 'ABS(EPOCH(try_strptime(\"dob_l\", \\'%Y-%m-%d\\')) - EPOCH(try_strptime(\"dob_r\", \\'%Y-%m-%d\\'))) <= 31557600.0',\n",
       "     'label_for_charts': 'Abs date difference <= 1 year',\n",
       "     'm_probability': 0.006199700352635373,\n",
       "     'u_probability': 0.03354178334846173},\n",
       "    {'sql_condition': 'ABS(EPOCH(try_strptime(\"dob_l\", \\'%Y-%m-%d\\')) - EPOCH(try_strptime(\"dob_r\", \\'%Y-%m-%d\\'))) <= 315576000.0',\n",
       "     'label_for_charts': 'Abs date difference <= 10 year',\n",
       "     'm_probability': 0.02726057331431288,\n",
       "     'u_probability': 0.25042855211572257},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.010524620053827111,\n",
       "     'u_probability': 0.6870793178701791}],\n",
       "   'comparison_description': 'DateOfBirthComparison'},\n",
       "  {'output_column_name': 'postcode_fake',\n",
       "   'comparison_levels': [{'sql_condition': '\"postcode_fake_l\" IS NULL OR \"postcode_fake_r\" IS NULL',\n",
       "     'label_for_charts': 'postcode_fake is NULL',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"postcode_fake_l\" = \"postcode_fake_r\"',\n",
       "     'label_for_charts': 'Exact match on full postcode',\n",
       "     'm_probability': 0.6753757141159644,\n",
       "     'u_probability': 0.00014903079407788038},\n",
       "    {'sql_condition': 'NULLIF(regexp_extract(\"postcode_fake_l\", \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]? [0-9]\\', 0), \\'\\') = NULLIF(regexp_extract(\"postcode_fake_r\", \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]? [0-9]\\', 0), \\'\\')',\n",
       "     'label_for_charts': 'Exact match on sector',\n",
       "     'm_probability': 0.09395110908506221,\n",
       "     'u_probability': 0.00028996941834157726},\n",
       "    {'sql_condition': 'NULLIF(regexp_extract(\"postcode_fake_l\", \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]?\\', 0), \\'\\') = NULLIF(regexp_extract(\"postcode_fake_r\", \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]?\\', 0), \\'\\')',\n",
       "     'label_for_charts': 'Exact match on district',\n",
       "     'm_probability': 0.04225883501370074,\n",
       "     'u_probability': 0.00048215845142843657},\n",
       "    {'sql_condition': 'NULLIF(regexp_extract(\"postcode_fake_l\", \\'^[A-Za-z]{1,2}\\', 0), \\'\\') = NULLIF(regexp_extract(\"postcode_fake_r\", \\'^[A-Za-z]{1,2}\\', 0), \\'\\')',\n",
       "     'label_for_charts': 'Exact match on area',\n",
       "     'm_probability': 0.09785854822786318,\n",
       "     'u_probability': 0.011052555271205699},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.09055579355740942,\n",
       "     'u_probability': 0.9880262860649464}],\n",
       "   'comparison_description': 'PostcodeComparison'},\n",
       "  {'output_column_name': 'birth_place',\n",
       "   'comparison_levels': [{'sql_condition': '\"birth_place_l\" IS NULL OR \"birth_place_r\" IS NULL',\n",
       "     'label_for_charts': 'birth_place is NULL',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"birth_place_l\" = \"birth_place_r\"',\n",
       "     'label_for_charts': 'Exact match on birth_place',\n",
       "     'm_probability': 0.8345399212327826,\n",
       "     'u_probability': 0.005228068621252938},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.1654600787672175,\n",
       "     'u_probability': 0.9947719313787471}],\n",
       "   'comparison_description': 'ExactMatch'},\n",
       "  {'output_column_name': 'occupation',\n",
       "   'comparison_levels': [{'sql_condition': '\"occupation_l\" IS NULL OR \"occupation_r\" IS NULL',\n",
       "     'label_for_charts': 'occupation is NULL',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"occupation_l\" = \"occupation_r\"',\n",
       "     'label_for_charts': 'Exact match on occupation',\n",
       "     'm_probability': 0.8982446653774181,\n",
       "     'u_probability': 0.037284666827814034},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.10175533462258204,\n",
       "     'u_probability': 0.9627153331721859}],\n",
       "   'comparison_description': 'ExactMatch'}]}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "linker.misc.save_model_to_json(\"model_h50k.json\", overwrite=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
